﻿-- =============================================
-- Script Template 专家方案
-- =============================================
if exists(select * from sysobjects where name='proc_ExpertNewsInfoPageList_Select')
drop proc proc_ExpertNewsInfoPageList_Select
go
CREATE PROCEDURE [proc_ExpertNewsInfoPageList_Select]
	@PageSize int=15,
    @PageIndex int=1,
    @SortFieldName nvarchar(50), --排序字段
    @SortDirection INT=0,	         --排序方向,
    @Extitle nvarchar(50)=null,
	@PlayId int=null	--玩法ID
	---@TotalCount int output
AS
	
	declare @sql nvarchar(max), @SortFieldNameSortDirection nvarchar(50);
	set @SortFieldNameSortDirection=isnull(@SortFieldName,'ID')+ltrim(@SortDirection);
	set @sql=
	'SELECT  * FROM 
		(SELECT 
			 Id, CreateTime, UserId, PlayId, PlayQiNo, Extitle, Keyword, Exmiaoshu, Uptime, Endtime, 
			 Scheme, ColourMoney, ClickSum, CheckMsg, ExperRemark,
			case '''+@SortFieldNameSortDirection+'''
				 		WHEN ''CreateTime0'' THEN ROW_NUMBER() OVER (ORDER BY ns.CreateTime DESC) 
						WHEN ''CreateTime1'' THEN ROW_NUMBER() OVER (ORDER BY ns.CreateTime) 
						WHEN ''ID0'' THEN ROW_NUMBER() OVER (ORDER BY ns.ID DESC) 
						WHEN ''ID1'' THEN ROW_NUMBER() OVER (ORDER BY ns.ID) 
						ELSE  ROW_NUMBER() OVER (ORDER BY ns.ID DESC)
						END AS Rn
		 FROM pm_expertNews ns
          WHERE (@Extitle is null or ns.Extitle LIKE N''%''+@Extitle+''%'')
		   AND (@PlayId is null or ns.PlayId=@PlayId)
		 ) 
		as r
	 WHERE r.Rn between (@PageIndex-1)*@PageSize+1 and @PageIndex*@PageSize; '
	PRINT @sql
	exec sp_executesql @sql,N'@PageSize int, @PageIndex int,@Extitle nvarchar(50),@PlayId int',
	 @PageSize,@PageIndex,@Extitle,@PlayId
	